Changing Data Types and Formatting Columns in Power BI

Course: Microsoft Power BI Training

Topic: Changing Data Types and Formatting Columns

Course Link: Microsoft Power BI Training at JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction

Changing data types and formatting columns are important steps in preparing data for analysis in Microsoft Power BI. Correct data types ensure that values behave appropriately in calculations, filtering, sorting, and relationships, while formatting makes those values easy to read in reports and dashboards.

For example, a Sales column should contain numeric values, an OrderDate column should use a date-related type, and a ProfitMargin column can be displayed as a percentage. These settings help users interpret business information accurately.

Power BI provides tools in Power Query Editor and the data model to manage data types and display formats. Understanding when to change a type and when to change only its format helps prevent data quality problems.

Learning Objectives

• Understand data types and their importance in Power BI.
• Identify commonly used Power Query and Power BI data types.
• Change data types in Power Query Editor and Table view.
• Format currency, numbers, percentages, dates, and text.
• Understand regional settings and locale-based conversions.
• Identify and troubleshoot conversion and formatting errors.
• Apply best practices for creating accurate reports.

2. Understanding Data Types

A data type defines the kind of value a column contains and how Power BI interprets that value. It influences which operations are appropriate for the column and how values are processed.

For example, the value 1000 could represent sales revenue, an employee identifier, or a postal code. Revenue normally requires a numeric type, while an identifier or postal code may need Text to preserve its original representation.

2.1 Importance of Correct Data Types

• Accurate calculations: Numeric columns can support sums, averages, and other calculations.
• Date analysis: Proper date types support chronological sorting and time-based reporting.
• Reliable sorting: Numbers sort numerically rather than alphabetically as text values can.
• Correct relationships: Related columns should have compatible data types and matching key values.
• Improved data quality: Conversion errors can reveal inconsistent source values.
• Efficient modeling: Appropriate types can support effective storage and model behavior.

2.2 Examples of Incorrect Data Types

3. Common Data Types in Power BI

Power Query Editor supports several data types. Some types are converted when data is loaded into the Power BI model, so the available types are not identical in every interface.

3.1 Text

Text stores characters, words, descriptions, and identifiers.

Examples: CustomerName, City, ProductCategory, EmployeeCode, and PostalCode.

Use Text when the values are labels or identifiers rather than quantities. Text is especially important when leading zeros must be preserved.

3.2 Whole Number

Whole Number stores integers without fractional parts.

Examples: Quantity, UnitsSold, NumberOfEmployees, and ItemCount.

Do not use Whole Number when fractional precision is required.

3.3 Decimal Number

Decimal Number stores numeric values that can contain fractional parts. In the Power BI model, this type uses floating-point representation, which can introduce small precision differences for some decimal calculations.

Examples: Weight, AverageRating, Measurements, and many analytical calculations.

3.4 Fixed Decimal Number

Fixed Decimal Number, also called Currency in some Power Query interfaces, is suitable for values requiring fixed decimal precision. The corresponding Currency type in the Power BI data model uses four decimal places of precision.

Examples: Transaction amounts, accounting values, and financial calculations requiring fixed decimal precision.

Choosing this type does not automatically display a currency symbol. The display format is configured separately.

3.5 Date

Date stores a calendar date without a time component.

Examples: OrderDate, JoiningDate, InvoiceDate, and BirthDate.

3.6 Time

Time stores a time-of-day value without a date.

Examples: OpeningTime, ShiftStartTime, and DeliveryTime.

3.7 Date/Time

Date/Time stores both a date and a time.

Examples: TransactionTimestamp, OrderCreatedAt, and LoginTimeStamp.

3.8 Date/Time/Timezone

Power Query supports Date/Time/Timezone values that include a time-zone offset. When loaded into the standard Power BI model, these values are converted to Date/Time, and the model does not automatically adjust them to the report viewer's local time zone.

3.9 Duration

Duration represents a length of time, such as the difference between two timestamps. When loaded into the Power BI model, a Duration value is converted to a Decimal Number.

3.10 True/False

True/False stores logical values.

Examples: IsActive, IsReturned, HasPaid, and IsCompleted.

3.11 Binary and Any

• Binary: Represents binary content. Binary columns are not supported as regular columns in the Power BI data model and generally need to be converted or removed before loading.
• Any: Represents a column without a specific type assignment in Power Query. Explicitly assigning appropriate types is recommended for final query outputs.

3.12 Data Type Reference Table

4. Changing Data Types in Power Query Editor

Power Query Editor allows you to change a column's data type before the data is loaded into the Power BI model. This is often the preferred place to correct data types because it makes the preparation process repeatable during refresh.

4.1 Step-by-Step Procedure

1. Open Power BI Desktop.
2. Import a dataset or open an existing report.
3. Select Home > Transform data.
4. Select the required query from the Queries pane.
5. Select the column you want to change.
6. Use the data type icon beside the column name or the Data Type dropdown in the ribbon.
7. Select the appropriate type, such as Text, Whole Number, Decimal Number, or Date.
8. Review the data preview and check for errors.
9. Select Close & Apply to apply the query changes.

4.2 Example: Converting Sales from Text to Number

Suppose the SalesAmount column contains numeric values imported as text.

After conversion, Power BI can perform numeric aggregations on valid values. If a value contains an unexpected symbol or an incompatible decimal separator, the conversion may produce an error.

4.3 Changing Types with M Code

Power Query uses the M language to describe data transformations. The following example changes column types explicitly:

= Table.TransformColumnTypes(
    Source,
    {
        {"Quantity", Int64.Type},
        {"SalesAmount", type number},
        {"OrderDate", type date},
        {"CustomerName", type text}
    }
)

This expression assumes that Source refers to a previous step and that the specified columns exist. The expression should be adapted to the actual query structure.

5. Changing Data Types in Table View

Power BI Desktop also allows you to change the data type of a column after it has been loaded into the data model.

5.1 Procedure

1. Open Power BI Desktop.
2. Navigate to Table view or Data view, depending on the interface version.
3. Select the required table.
4. Select the column to modify.
5. Open the Column tools tab.
6. Locate the Data type dropdown.
7. Select the appropriate type.
8. Confirm any conversion warning if prompted.
9. Check dependent calculations, relationships, and visuals.

Note: A type change in the model differs from a transformation in Power Query. When the source data requires cleaning or consistent conversion, performing the transformation in Power Query is usually preferable.

6. Automatic Data Type Detection

Power Query can automatically detect column types when connecting to data sources. For structured sources such as databases, it can use the source schema. For unstructured sources such as CSV and Excel files, it can inspect sample values.

For common unstructured-source workflows, automatic type and header detection typically examines the first 200 rows. Therefore, the detected type may not represent unusual values that occur later in the dataset.

6.1 Example

6.2 Best Practices

• Review automatically generated Changed Type steps.
• Inspect columns containing mixed data types.
• Validate date interpretations against the source.
• Explicitly set types for important output columns.
• Use Text for codes and identifiers when numeric calculations are not required.

7. Changing Data Types Using Locale

Regional settings affect how text-based dates and numbers are interpreted. Locale-based conversion is particularly important when data comes from systems that use different date conventions or decimal separators.

7.1 Example: Date Interpretation

The text value 04/05/2026 can mean different dates depending on the source convention.

7.2 Procedure

1. Open Power Query Editor.
2. Right-click the column heading.
3. Select Change Type > Using Locale, or use the equivalent option in the data type menu.
4. Select the required data type.
5. Choose the locale that matches the source data.
6. Confirm the conversion and validate the results.

7.3 Decimal Separator Example

Selecting the wrong locale may cause a conversion error or an incorrect result. Always verify the source convention before converting values.

8. Understanding Column Formatting

Column formatting controls how values appear in reports and tables. Formatting can make information easier to read without changing the underlying numeric value.

For example, a stored numeric value of 0.25 can be displayed as 25% using percentage formatting. The underlying value remains 0.25.

8.1 Common Formatting Options

• Currency symbols and decimal places.
• Percentage formatting.
• Thousands separators.
• Date and time display patterns.
• Numeric precision in the displayed result.
• Column names and descriptions.
• Default summarization and data categories, where applicable.

9. Difference Between Data Type and Format

9.1 Example

Suppose the underlying value is 1250.5.

• General number display: 1,250.50
• Currency display: ₹1,250.50
• Percentage display: 125,050% if the numeric value is formatted directly as a percentage.

The percentage example demonstrates why the underlying value must match the intended meaning before formatting is applied. Currency formatting does not convert the value into a different currency.

10. Formatting Numeric Columns

Numeric formatting is important for sales reports, financial dashboards, inventory analysis, and operational reporting.

10.1 Whole Number Formatting

Whole-number formatting is suitable for quantities and counts.

Example: A UnitsSold column can display 250 instead of 250.00.

10.2 Decimal Places

Decimal-place settings determine how many digits appear after the decimal separator.

Display formatting may round the shown value. It does not necessarily round the underlying value used in calculations.

10.3 Thousands Separators

• Without grouping: 1250000
• With grouping: 1,250,000

Thousands separators improve readability for large numbers. The separator style depends on regional settings.

11. Formatting Currency Columns

Currency formatting is commonly used for Sales, Revenue, Cost, Profit, Salary, and Budget columns.

11.1 Steps

1. Select the numeric column in Table view or Data view.
2. Open Column tools.
3. Confirm that the column has a suitable numeric data type.
4. Locate the Format dropdown.
5. Select the required currency format when available.
6. Set the desired decimal places.
7. Review the results in report visuals.

11.2 Example

These examples use Indian rupee formatting. Actual display depends on the selected format, locale, and visual settings.

Important: Currency formatting does not perform exchange-rate conversion. If a dataset contains different currencies, use an appropriate conversion process before aggregating amounts into a common currency.

12. Formatting Percentage Columns

Percentage formatting is useful for ProfitMargin, DiscountRate, ConversionRate, and GrowthRate.

12.1 Understanding Percentage Values

12.2 Steps

1. Select the numeric column or measure.
2. Open Column tools or Measure tools, depending on the selected field.
3. Verify that the underlying values represent ratios.
4. Select Percentage from the Format options.
5. Set the desired decimal places.
6. Check the displayed results against the expected percentages.

Common mistake: If the source stores 25 to represent 25%, applying percentage formatting directly produces 2500%. Convert the source values to ratios first, for example by dividing them by 100.

13. Formatting Date and Time Columns

Date and time formatting makes temporal information easier to understand while preserving the underlying date-related behavior.

13.1 Common Date Formats

13.2 Steps

1. Select the date column in Table view or Data view.
2. Confirm that its type is Date or Date/Time, as required.
3. Open Column tools.
4. Select an available date format.
5. Review the displayed dates.
6. Verify chronological sorting and date filtering.

Changing the display format does not repair an incorrectly interpreted date. If a text date was converted using the wrong locale, correct the conversion in Power Query first.

14. Formatting and Cleaning Text Columns

Text fields often require cleaning to ensure consistent labels, categories, and names. These transformations change the text values themselves rather than merely changing their display format.

14.1 Common Text Transformations

• Trim: Removes leading and trailing spaces.
• Clean: Removes certain non-printable characters.
• Uppercase: Converts text to uppercase.
• Lowercase: Converts text to lowercase.
• Capitalize Each Word: Capitalizes the beginning of each word.
• Replace Values: Replaces selected values with standardized alternatives.

14.2 Example

Apply text cleaning carefully when punctuation, spaces, or capitalization may be meaningful to the source system.

15. Formatting Columns and Measures

Power BI columns and measures have different purposes, and their formatting options are accessed in different contexts.

15.1 Columns

A column contains values stored in a table. Examples include OrderDate, SalesAmount, Quantity, and ProductCategory.

15.2 Measures

A measure calculates a result dynamically according to the current filter context. Measures are commonly used for totals, averages, ratios, and key performance indicators.

15.3 Example Measure

Total Sales = SUM(Sales[SalesAmount])

To format the measure as currency:

1. Select the Total Sales measure in the Data or Model pane.
2. Open Measure tools.
3. Choose the required currency format.
4. Set the decimal places.
5. Use the measure in a visual and validate its display.

Formatting the measure changes the presentation of its result, not the underlying DAX calculation.

16. Column Names, Summarization, and Data Categories

16.1 Rename Columns

Clear column names help report users understand the meaning of a field.

1. Select the column in Power Query Editor or the data model.
2. Choose Rename or use the available column renaming option.
3. Enter a clear, business-friendly name.
4. Review dependent expressions and visuals.

For example, Cust_ID can be renamed Customer ID, and Net_Sales_Amt can be renamed Net Sales Amount.

16.2 Default Summarization

Numeric columns may be summarized automatically when added to a visual. Power BI may offer Sum, Average, Minimum, Maximum, or other aggregation options.

Summing Revenue is generally meaningful, but summing EmployeeID usually is not. Identifier columns should use an appropriate option such as Do not summarize when applicable.

16.3 Data Categories

Data categories help Power BI interpret fields such as City, Country/Region, Postal Code, Web URL, and Image URL. Assigning an appropriate category can improve supported visual behavior, but it does not replace choosing the correct data type.

17. Common Errors and Troubleshooting

17.1 Systematic Troubleshooting

1. Open Power Query Editor and select the affected query.
2. Locate the first step where values become incorrect or an error appears.
3. Inspect the preceding step to identify whether the source values were valid.
4. Check column names, data types, regional settings, and unexpected characters.
5. Correct the conversion or source data issue.
6. Inspect later steps for additional errors.
7. Apply the changes and validate the loaded data and report visuals.

18. Practical Project: Prepare a Sales Dataset

18.1 Sample Dataset

18.2 Required Tasks

1. Import the dataset into Power BI Desktop.
2. Open Power Query Editor.
3. Set OrderID to Whole Number if it is a numeric key without leading zeros or special formatting requirements.
4. Set OrderDate to Date.
5. Set Product to Text.
6. Set Quantity to Whole Number.
7. Set SalesAmount to a suitable numeric type.
8. Set ProfitMargin to Decimal Number.
9. Select Close & Apply.
10. Format SalesAmount as the required currency.
11. Format ProfitMargin as Percentage.
12. Verify the results in a table visual.

18.3 Expected Display

The example uses Indian rupee formatting and percentage ratios. The underlying ProfitMargin values are stored as decimals, not as whole-number percentage points.

19. Best Practices

• Verify data types before building report visuals.
• Use Text for postal codes, telephone numbers, and identifiers that may contain leading zeros.
• Use Date or Date/Time for actual date values rather than retaining them as text.
• Choose numeric types according to calculation and precision requirements.
• Use locale-based conversion when regional conventions differ.
• Apply currency and percentage formatting only after validating underlying values.
• Use consistent decimal places across related financial fields.
• Review default summarization for each numeric column.
• Check calculations, relationships, and visuals after type changes.
• Do not use formatting to hide data quality problems.
• Convert or remove unsupported Binary columns before loading when necessary.
• Test refreshes to confirm that incoming data continues to match the expected schema.

20. Interview Questions and Answers

Q1. What is a data type in Power BI?

Answer: A data type defines how a column's values are interpreted and used in calculations, relationships, and visualizations.

Q2. What is the difference between Whole Number and Decimal Number?

Answer: Whole Number stores integers, whereas Decimal Number supports fractional values using floating-point representation.

Q3. What is Fixed Decimal Number?

Answer: It is a numeric type intended for fixed decimal precision. The corresponding Currency type in the Power BI model uses four decimal places.

Q4. Does currency formatting change the underlying value?

Answer: No. Currency formatting changes the presentation of a numeric value, not the amount or its currency denomination.

Q5. Why should postal codes often use Text?

Answer: Postal codes are identifiers rather than quantities. Text preserves leading zeros and supports codes containing letters.

Q6. What is locale-based conversion?

Answer: It interprets dates and numbers according to regional conventions, reducing errors caused by different date and decimal formats.

Q7. What is the difference between a column and a measure?

Answer: A column contains values in a table, while a measure calculates a result dynamically according to the current filter context.

Q8. Why can percentage formatting produce incorrect results?

Answer: Percentage formatting expects a ratio. Formatting 0.25 produces 25%, while formatting 25 produces 2500%.

Q9. Can formatting fix an incorrect data type?

Answer: No. Formatting controls display, while the underlying type must be corrected separately.

Q10. What is automatic data type detection?

Answer: It is a feature that identifies likely column types using source metadata or sample values. The detected types should be validated.

Q11. What happens if numeric values are stored as text?

Answer: Sorting, aggregation, and calculations may behave incorrectly until the values are converted to a suitable numeric type.

Q12. When should Date/Time be used instead of Date?

Answer: Use Date when only the calendar date matters and Date/Time when both date and time are required.

21. Quick Revision Checklist

• Identify the correct type for every column.
• Convert text-based numbers and dates where appropriate.
• Preserve leading zeros in identifiers using Text.
• Use locale settings when regional interpretation is ambiguous.
• Format currency, percentages, and dates for readability.
• Remember that display formatting normally does not change stored values.
• Validate percentage ratios before applying percentage formatting.
• Review default summarization and data categories.
• Test calculations, relationships, and visuals after type changes.
• Validate the final results after refresh.

22. Conclusion

Changing data types and formatting columns are foundational skills for building reliable Power BI reports. Correct data types support accurate calculations, sorting, filtering, and relationships, while suitable formatting makes report values easier to interpret.

Use Power Query Editor to prepare and convert source data, and use Column tools or Measure tools to configure model-level data types and display formats. Always validate the underlying values before formatting dates, currencies, and percentages.

Additional Learning Resources

• Microsoft Power BI Training at JustAcademy
• Register for a Course Demo
• Visit JustAcademy
• Microsoft Learn: Data Types in Power Query
• Microsoft Learn: Data Types in Power BI